﻿Imports System.Data.SqlClient

Public Enum SQLServerSysTypes
    image = 34
    text = 35
    uniqueidentifier = 36
    tinyint = 48
    smallint = 52
    int = 56
    smalldatetime = 58
    real = 59
    money = 60
    datetime = 61
    float = 56
    sql_variant = 98
    ntext = 99
    bit = 104
    [decimal] = 106
    numeric = 108
    smallmoney = 122
    bigint = 127
    varbinary = 165
    varchar = 167
    binary = 173
    [char] = 175
    timestamp = 189
    nvarchar = 231
    nchar = 239
    xml = 241
    sysname = 231
End Enum

'colstat = 1 = identity.

Public Class Column
    Inherits DatabaseObject
    Public ID As Long
    Public IsNullable As Boolean
    Public SQLServerXtype As Long
    Public ColumnOrder As Long
    Public IsIdentity As Boolean
    Public SQLServerType As SQLServerSysTypes
    Public AllowNulls As Boolean = False
    Public Length As String
    Public SQLServerColStat As Long
    Public ShowInMasterView As Boolean = False
    Public ShowInDetailView As Boolean = False
    Public SearchType As String = ""
    Public IsGroupColumn As Boolean = False
    Public LinkTableName As String = ""
    Public LinkTableKey As String = ""
    Public LinkTableValue As String = ""


    Public Sub New()
        Type = DatabaseObjectType.Column
    End Sub

    Public Sub New(ByVal Reader As SqlDataReader)
        Type = DatabaseObjectType.Column
        Name = Reader("Name")
        ID = Reader("ID")
        If Reader("ColStat") = 1 Then
            IsIdentity = True
        Else
            ShowInDetailView = True
            ShowInMasterView = True
        End If
        SQLServerType = Reader("xtype")
        AllowNulls = Reader("Isnullable")

        Length = Reader("Length")
        If Length = "-1" Then
            Length = ""
        End If
        SQLServerColStat = Reader("ColStat")
        SQLServerXtype = Reader("xtype")

    End Sub

    Public Function ColumnTypeName() As String
        Select Case SQLServerType
            Case 34
                Return "image"
            Case 35
                Return "text"
            Case 36
                Return "uniqueidentifier"
            Case 48
                Return "tinyint"
            Case 52
                Return "smallint"
            Case 56
                Return "int"
            Case 58
                Return "smalldatetime"
            Case 59
                Return "real"
            Case 60
                Return "money"
            Case 61
                Return "datetime"
            Case 56
                Return "float"
            Case 98
                Return "sql_variant"
            Case 99
                Return "ntext"
            Case 104
                Return "bit"
            Case 106
                Return "decimal"
            Case 108
                Return "numeric"
            Case 122
                Return "smallmoney"
            Case 127
                Return "bigint"
            Case 165
                Return "varbinary"
            Case 167
                Return "varchar"
            Case 173
                Return "binary"
            Case 175
                Return "char"
            Case 189
                Return "timestamp"
            Case 231
                Return "nvarchar"
            Case 239
                Return "nchar"
            Case 241
                Return "xml"
            Case 231
                Return "sysname"
            Case Else
                Debug.WriteLine("Column.ColumnTypeName" & SQLServerType & " not found")
                Return ""
        End Select
    End Function

    Public Function IsStringType() As Boolean
        IsStringType = True
        Select Case SQLServerType
            Case SQLServerSysTypes.char
            Case SQLServerSysTypes.nchar
            Case SQLServerSysTypes.ntext
            Case SQLServerSysTypes.nvarchar
            Case SQLServerSysTypes.sql_variant
            Case SQLServerSysTypes.sysname
            Case SQLServerSysTypes.text
            Case SQLServerSysTypes.varchar
            Case Else
                IsStringType = False
        End Select

    End Function

    Public Overrides Function ToString() As String
        Dim Result As String = Name
        Result = Result & " ("
        Result = Result & ColumnTypeName()
        If IsStringType() Then
            Result = Result & "(" & Length & ")"
        End If
        Result = Result & ", "
        If IsNullable Then
            Result = Result & "null)"
        Else
            Result = Result & "not null)"
        End If

        Return Result
    End Function

    Public Function GetColumnTypeName() As String
        If SQLServerColStat = 1 Then
            Return "autonumber"
        End If
        Select Case SQLServerXtype
            Case 34
                Return "image"
            Case 35
                Return "text"
            Case 36
                Return "uniqueidentifier"
            Case 48
                Return "tinyint"
            Case 52
                Return "smallint"
            Case 56
                Return "int"
            Case 58
                Return "smalldatetime"
            Case 59
                Return "real"
            Case 60
                Return "money"
            Case 61
                Return "datetime"
            Case 56, 62
                Return "float"
            Case 98
                Return "sql_variant"
            Case 99
                Return "ntext"
            Case 104
                Return "bit"
            Case 106
                Return "decimal"
            Case 108
                Return "numeric"
            Case 122
                Return "smallmoney"
            Case 127
                Return "bigint"
            Case 165
                Return "varbinary"
            Case 167
                Return "varchar"
            Case 173
                Return "binary"
            Case 175
                Return "char"
            Case 189
                Return "timestamp"
            Case 231
                Return "nvarchar"
            Case 239
                Return "nchar"
            Case 241
                Return "xml"
            Case 231
                Return "sysname"
            Case Else
                Debug.WriteLine("Column.ColumnTypeName" & SQLServerXtype & " not found")
                Return ""
        End Select
    End Function


End Class